import sys

import xlrd
import xlwt

from xlutils import copy


from PyQt6.QtCore import pyqtSignal
from PyQt6.QtWidgets import QApplication, QWidget, QFileDialog, QPushButton, QHBoxLayout, QMessageBox,QVBoxLayout,QLabel,QComboBox


class Pyqt6(QWidget):
    widget = ""
    old_field_list = {}
    new_field_list = {}

    def __init__(self):
        super().__init__()
        self.initUI()
        self.view_ui = listViewUI()
        self.init_connection()

    def init_connection(self):
        self.view_ui.back_signal.connect(self.backSuccess)
        self.widget.startBtn.clicked.connect(self.toViewPage)

    def backSuccess(self):
        self.view_ui.hide()
        self.show()
    def toViewPage(self):
        self.hide()
        self.view_ui.show()
    def handlerExcel(self):
        if self.widget.olabel.text() == '--' or self.widget.nlabel.text() == '--':
            QMessageBox.warning(self, "错误", "检查原文件和新文件是否上传")
            return
        old_excel_path = self.widget.olabel.text()
        new_excel_path = self.widget.nlabel.text()
        self.old_field_list = self.handlerHeader(old_excel_path)
        self.new_field_list = self.handlerHeader(new_excel_path)
        self.view_ui.initData(self.old_field_list,self.new_field_list,old_excel_path,new_excel_path)


    def handlerHeader(self,path):
        old_workbook = xlrd.open_workbook(path)
        old_sheet = old_workbook.sheet_by_index(0)
        header_line = old_sheet.row(0)
        headerDict = {}
        for i in range(header_line.__len__()):
            value = header_line[i].value
            headerDict[i] = value
        return headerDict

    def clickButton(self):
        sender = self.sender()
        # 打开文件管理器
        text = sender.text()
        if text == '原文件':
            fd = QFileDialog.getOpenFileNames(self, '选择excel', './', 'ALL(*.*)', 'xls文件（*.xls）')
            self.widget.olabel.setText(fd[0][0])
        elif text == '新文件':
            fd = QFileDialog.getOpenFileNames(self, '选择excel', './', 'ALL(*.*)', 'xls文件（*.xls）')
            self.widget.nlabel.setText(fd[0][0])
        elif text == '开始解析':
            self.handlerExcel()

    def initUI(self):
        self.setWindowTitle("我的工具cc")
        self.resize(500, 500)
        self.widget =  QWidget()
        self.widget.olabel = QLabel("--")

        self.widget.nlabel = QLabel("--")

        # 修改
        self.widget.oldBtn = QPushButton('原文件')
        self.widget.oldBtn.setStyleSheet(''' text-align : center;
                                                      background-color : NavajoWhite;
                                                      width:20px;
                                                      height : 30px;
                                                      border-style: outset;
                                                      font : 13px  ''')
        self.widget.oldBtn.clicked.connect(self.clickButton)
        # 删除
        self.widget.newBtn = QPushButton('新文件')
        self.widget.newBtn.setStyleSheet(''' text-align : center;
                                                background-color : LightCoral;
                                                width:20px;
                                                height : 30px;
                                                border-style: outset;
                                                font : 13px; ''')
        self.widget.newBtn.clicked.connect(self.clickButton)

        self.widget.startBtn = QPushButton("开始解析")
        self.widget.startBtn.setStyleSheet(''' text-align : center;
                                                background-color : LightCoral;
                                                width:20px;
                                                height : 30px;
                                                border-style: outset;
                                                font : 13px; ''')
        self.widget.startBtn.clicked.connect(self.clickButton)

        qvlayout = QVBoxLayout()

        hLayout = QHBoxLayout()
        hLayout.addWidget(self.widget.oldBtn)
        hLayout.addWidget(self.widget.newBtn)
        hLayout.addWidget(self.widget.startBtn)

        text1Layout = QHBoxLayout()
        text1Layout.addWidget(self.widget.olabel)


        text2Layout = QHBoxLayout()
        text2Layout.addWidget(self.widget.nlabel)

        qvlayout.addLayout(text1Layout)
        qvlayout.addLayout(text2Layout)
        qvlayout.addLayout(hLayout)
        self.setLayout(qvlayout)
        self.show()

class listViewUI(QWidget):
    back_signal = pyqtSignal(str)
    box = {}
    relation = {}

    def __init__(self):
        super().__init__()
        self.init_ui()
        self.init_connection()
        self.setWindowTitle("处理数据关系")
        self.setFixedSize(800,800)
    def init_ui(self):
        self.backBtn = QPushButton("返回",self)
        self.backBtn.move(10,10)

    def init_connection(self):
        self.backBtn.clicked.connect(self.doBack)

    def doBack(self):
        self.back_signal.emit("back")
    def initData(self,oldArray,newArray,oldPath,newPath):
        self.oldPath = oldPath
        self.newPath = newPath
        qvlayout = QVBoxLayout()
        for nk,value in newArray.items():
            qhbox = QHBoxLayout()
            button = QPushButton(value)
            qhbox.addWidget(button)
            box = QComboBox()
            self.box[box] = nk
            for ok in oldArray.values():
                box.addItem(ok)
            box.setCurrentIndex(-1)
            box.currentIndexChanged.connect(self.selectValue)
            qhbox.addWidget(box)
            qvlayout.addLayout(qhbox)
         #执行按钮
        qhbox = QHBoxLayout()
        exec_btn = QPushButton("执行同步数据")
        exec_btn.setStyleSheet(''' text-align : center;
                                                background-color : LightCoral;
                                                width:20px;
                                                height : 30px;
                                                border-style: outset;
                                                font : 13px; ''')
        exec_btn.clicked.connect(self.wirteExcel)

        qhbox.addWidget(exec_btn)
        qvlayout.addLayout(qhbox)
        self.setLayout(qvlayout)
    def selectValue(self):
        sender = self.sender()
        index = self.box[sender]
        self.relation[index] = sender.currentIndex()

    def wirteExcel(self):
        oldWorkbook = xlrd.open_workbook(self.oldPath)
        newWorkbook = xlrd.open_workbook(self.newPath)
        newWirteExcel = copy.copy(newWorkbook)
        wirteSheet = newWirteExcel.get_sheet(0)
        sheetZone = oldWorkbook.sheet_by_index(0)
        print(self.relation)
        for i in range(sheetZone.nrows):
            if i==0:
                continue
            for j in range(self.relation.__len__()):
                if j in self.relation.keys():
                    j_ = self.relation[j]
                    value = sheetZone.cell(i, j_).value
                    wirteSheet.write(i,j,value)
        # 完成
        newWirteExcel.save(self.newPath)
        QMessageBox.information(self, "成功", "写入成功")




if __name__ == '__main__':
    q_application = QApplication(sys.argv)
    pyqt_ = Pyqt6()
    sys.exit(q_application.exec())

